/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package checkers.database;

import checkers.businessLogic.entity.User;
import checkers.exceptions.EmailNotUniqueException;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import java.sql.*;
import java.util.LinkedList;

/**
 *
 * @author artinshahverdian
 */
public class MySQLUserOperations implements IDatabaseUserOperations {

    private static MySQLUserOperations instance = null;
    private ConnectionPool connectionPool;

    private MySQLUserOperations() throws Exception {
        connectionPool = ConnectionPool.getInstance();
    }

    public static MySQLUserOperations GetInstance() throws Exception {
        if (instance == null)
            instance = new MySQLUserOperations();
        return instance;
    }

    @Override
    public void registerUser(User user) throws EmailNotUniqueException, SQLException {


        String query = "INSERT INTO user "
                + "(email,password,first_name,last_name,birth_date,security_question,security_answer,rating,last_login,registration_date)"
                + " VALUES ('" + user.getEmail() + "','"
                + user.getPassword() + "','" + user.getFirstName() + "','"
                + user.getLastName() + "','" + user.getBirthDate() + "',\"" + user.getSecurityQuestion() + "\",\"" + user.getSecurityAnswer() + "\"," + user.getRating() + ","
                + "null" + ",'" + user.getRegistrationDate() + "');";
        try {
            executeUpdate(query);
        }
        catch (MySQLIntegrityConstraintViolationException e) {
            throw new EmailNotUniqueException();
        }


    }

    @Override
    public void updateEmail(String userId, String newEmail) throws SQLException {
        String query = "UPDATE user SET email='" + newEmail + "' WHERE id='" + userId + "';";
        executeUpdate(query);
    }

    @Override
    public void updateFirstName(String userId, String firstName) throws SQLException {

        String query = "UPDATE user SET first_name='" + firstName + "' WHERE id='" + userId + "';";
        executeUpdate(query);

    }

    @Override
    public void updateLastName(String userId, String lastName) throws SQLException {

        String query = "UPDATE user SET last_name='" + lastName + "' WHERE id='" + userId + "';";
        executeUpdate(query);
    }

    @Override
    public void updateBirthDate(String userId, Date birthDate) throws SQLException {

        String query = "UPDATE user SET birth_date='" + birthDate + "' WHERE id='" + userId + "';";
        executeUpdate(query);
    }

    @Override
    public void updateSecurityQuestion(String userId, String securityQuestion) throws SQLException {

        String query = "UPDATE user SET security_question=\"" + securityQuestion + "\" WHERE id='" + userId + "';";
        executeUpdate(query);

    }

    @Override
    public void updatePassword(String userId, String password) throws SQLException {

        String query = "UPDATE user SET password='" + password + "' WHERE id='" + userId + "';";
        executeUpdate(query);

    }

    @Override
    public void updateRating(String userId, int rating) throws SQLException {

        String query = "UPDATE user SET rating=" + rating + " WHERE id='" + userId + "';";
        executeUpdate(query);
    }

    @Override
    public void updateSecurityAnswer(String userId, String securityAnswer) throws SQLException {

        String query = "UPDATE user SET security_answer='" + securityAnswer + "' WHERE id='" + userId + "';";
        executeUpdate(query);

    }

    @Override
    public void updateLastLogin(String userId, Timestamp lastLogin) throws SQLException {

        String query = "UPDATE user SET last_login='" + lastLogin + "' WHERE id='" + userId + "';";

        executeUpdate(query);

    }

    @Override
    public User getUser(String email, String password) throws SQLException {

        Connection connection = connectionPool.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        String query = "SELECT * FROM user WHERE email='" + email + "' AND password='" + password + "';";
        User user = new User();


        try {

            resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setEmail(resultSet.getString("email"));
                user.setFirstName(resultSet.getString("first_name"));
                user.setLastName(resultSet.getString("last_name"));
                user.setBirthDate(resultSet.getDate("birth_date"));
                user.setSecurityQuestion(resultSet.getString("security_question"));
                user.setPassword(resultSet.getString("password"));
                user.setRating(resultSet.getInt("rating"));
                user.setSecurityAnswer(resultSet.getString("security_answer"));
                user.setLastLogin(resultSet.getTimestamp("last_login"));
                user.setRegistrationDate(resultSet.getTimestamp("registration_date"));
            }
            else
                return null;
        }
        finally {
            resultSet.close();
            statement.close();
            connection.close();
        }

        return user;
    }

    @Override
    public String getSecurityQuestion(String email) throws SQLException {

        String result = (String) getColumnValue("security_question", "email", email);
        return result;


    }

    @Override
    public String getSecurityAnswer(String email) throws SQLException {

        String result = (String) getColumnValue("security_answer", "email", email);
        return result;
    }

    @Override
    public String getPassword(String email) throws SQLException {

        String result = (String) getColumnValue("password", "email", email);
        return result;
    }

    @Override
    public String getName(String userId) throws SQLException {

        String result = (String) getColumnValue("first_name", "id", userId);
        return result;
    }

    public String getEmail(String userId) throws SQLException {

        String result = (String) getColumnValue("email", "id", userId);
        return result;
    }

    @Override
    public Integer getRating(String userId) throws SQLException {

        Integer result = (Integer) getColumnValue("rating", "id", userId);
        return result;
    }

    public Integer getId(String email) throws SQLException {
        Integer result = (Integer) getColumnValue("id", "email", email);
        return result;
    }

    public LinkedList<User> getHighRanking() throws SQLException {

        Connection connection = connectionPool.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        LinkedList<User> highRatingUsers = new LinkedList<User>();
        String query = "SELECT * FROM user ORDER BY rating DESC;";





        try {
            resultSet = statement.executeQuery(query);


            int i = 0;
            while (resultSet.next() && i++ <= 10) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setEmail(resultSet.getString("email"));
                user.setFirstName(resultSet.getString("first_name"));
                user.setLastName(resultSet.getString("last_name"));
                user.setBirthDate(resultSet.getDate("birth_date"));
                user.setSecurityQuestion(resultSet.getString("security_question"));
                user.setPassword(resultSet.getString("password"));
                user.setRating(resultSet.getInt("rating"));
                user.setSecurityAnswer(resultSet.getString("security_answer"));
                user.setLastLogin(resultSet.getTimestamp("last_login"));
                user.setRegistrationDate(resultSet.getTimestamp("registration_date"));
                highRatingUsers.add(user);
            }
        }
        finally {
            resultSet.close();
            statement.close();
            connection.close();

        }
        return highRatingUsers;

    }

    private void executeUpdate(String query) throws SQLException {

        Connection connection = connectionPool.getConnection();
        Statement statement = connection.createStatement();

        try {
            statement.executeUpdate(query);
        }
        finally {
            connection.close();
            statement.close();
        }


    }

    private Object getColumnValue(String ColumnName, String whereColumnName, String whereValue) throws SQLException {

        Connection connection = connectionPool.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        String query = "SELECT " + ColumnName + " FROM user WHERE " + whereColumnName + "='" + whereValue + "';";
        Object result = null;




        try {
            resultSet = statement.executeQuery(query);
            if (resultSet.next())
                result = resultSet.getObject(ColumnName);
        }
        finally {
            resultSet.close();
            statement.close();
            connection.close();

        }
        return result;

    }

    @Override
    public User getUserById(String UserId) throws SQLException {
        Connection connection = connectionPool.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        String query = "SELECT * FROM user WHERE id='" + UserId + "';";
        User user = new User();


        try {

            resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setEmail(resultSet.getString("email"));
                user.setFirstName(resultSet.getString("first_name"));
                user.setLastName(resultSet.getString("last_name"));
                user.setBirthDate(resultSet.getDate("birth_date"));
                user.setSecurityQuestion(resultSet.getString("security_question"));
                user.setPassword(resultSet.getString("password"));
                user.setRating(resultSet.getInt("rating"));
                user.setSecurityAnswer(resultSet.getString("security_answer"));
                user.setLastLogin(resultSet.getTimestamp("last_login"));
                user.setRegistrationDate(resultSet.getTimestamp("registration_date"));
            }
            else
                return null;
        }
        finally {
            resultSet.close();
            statement.close();
            connection.close();
        }

        return user;
    }
}
